package cn.edu.sdjzu.xg2106.dao;

import cn.edu.sdjzu.xg2106.domain.School;
import cn.edu.sdjzu.xg2106.domain.University;
import cn.edu.sdjzu.xg2106.exception.BysjException;
import cn.edu.sdjzu.xg2106.service.UniversityService;
import cn.edu.sdjzu.xg2106.util.Condition;
import cn.edu.sdjzu.xg2106.util.JdbcHelper;
import cn.edu.sdjzu.xg2106.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.TreeSet;

public class SchoolDao {
    private SchoolDao(){}
    public static SchoolDao schoolDao = null;
    public static SchoolDao getInstance(){
        if (schoolDao==null){
            schoolDao=new SchoolDao();
        }
        return schoolDao;
    }

    public boolean delete(Long id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        PreparedStatement preparedStatement =
                connection.prepareStatement("delete from school " +
                        " where id=?");
        preparedStatement.setLong(1, id);
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement, connection);
        return affectedRowNum > 0;
    }
    public boolean add(School school, Connection connection)throws SQLException {
        PreparedStatement preparedStatement=connection.prepareStatement(
                "insert into school"+
                        "(id,description,no,remarks,university_id)"+
                        "values(?,?,?,?,?)");
        preparedStatement.setLong(1,school.getId());
        preparedStatement.setString(2,school.getDescription());
        preparedStatement.setString(3,school.getNo());
        preparedStatement.setString(4,school.getRemarks());
        preparedStatement.setLong(5,school.getUniversity_id());
        int rowAffected = preparedStatement.executeUpdate();
        return rowAffected>0;
    }
    public boolean update(School  school) throws SQLException {
        Connection connection=JdbcHelper.getConn();
        String sql_update=
                "update school set description=?,no=?,remarks=?,university_id=? where id=?";
        PreparedStatement preparedStatement_update = connection.prepareStatement(sql_update);
        preparedStatement_update.setString(1,school.getDescription());
        preparedStatement_update.setString(2,school.getNo());
        preparedStatement_update.setString(3,school.getRemarks());
        preparedStatement_update.setLong(4,school.getUniversity_id());
        preparedStatement_update.setLong(5,school.getId());
        int rowAffected = preparedStatement_update.executeUpdate();
        JdbcHelper.close(preparedStatement_update,connection);
        return rowAffected>0;
    }
    public School find(Integer id,Connection connection) throws SQLException {
        School dDepartment = null;
        String select_s = "select * from school WHERE id=?";
        PreparedStatement p1 = connection.prepareStatement(select_s);
        p1.setLong(1,id);
        ResultSet resultSet = p1.executeQuery();
        if (resultSet.next()){
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int uniId = resultSet.getInt("university_id");
            dDepartment = new School(id,description,no,remarks,uniId);
        }
        JdbcHelper.close(resultSet,p1,connection);
        return dDepartment;
    }

    public School find1(int id)throws SQLException {
        School desiredSchool = null;
        Connection connection= JdbcHelper.getConn();
        String sql_select_id = "select * from school where id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setLong(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            int university_id = resultSet_select_id.getInt("university_id");
            University university = UniversityService.getInstance().find(university_id);
            desiredSchool = new School(id,description,no,remarks,university);
            System.out.println(description);
        }
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return  desiredSchool;
    }



    public Collection<School> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException, BysjException {
        Collection<School>desiredSchools = new HashSet<School>();
        StringBuilder sql_select = new StringBuilder("select * from school");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"school",conditionList);
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();

        while(resultSet_select.next()){
            int id = resultSet_select.getInt("id");
            String description = resultSet_select.getString("description");
            String no = resultSet_select.getString("no");
            String remarks = resultSet_select.getString("remarks");
            int university_id = resultSet_select.getInt("university_id");
            University university =UniversityService.getInstance().find(university_id);
            School desiredSchool = new School(id,description,no,remarks,university);
            desiredSchools.add(desiredSchool);
        }
        JdbcHelper.close(resultSet_select,pstmt_select);
        return desiredSchools;
    }


}
